﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ProjectSell
{
    public class clsDatabase
    {
        private SqlConnection sqlCon;
        private DataTable dtt;

        public string strError { get; set; }

        public clsDatabase()
        {
            try
            {
                if (sqlCon == null)
                {
                    sqlCon = new SqlConnection();
                    sqlCon.ConnectionString = ConfigurationManager.ConnectionStrings["Scam"].ConnectionString;
                    if (sqlCon.State == ConnectionState.Closed)
                    {
                        sqlCon.Open();
                    }
                }


            }
            catch (Exception ex)
            {
                strError = ex.Message;
                Console.WriteLine("Ket noi:" + ex.Message);
            }
        }


        public DataTable FillData(string sql)
        {
            try
            {
                if (dtt == null)
                {
                    dtt = new DataTable();
                    SqlDataAdapter ad = new SqlDataAdapter(sql, sqlCon);
                    ad.Fill(dtt);

                    return dtt;

                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                Console.WriteLine("Fill data:" + ex.Message);
            }
            return null;
        }


        public int Execute(string sql)
        {
            try
            {
                SqlCommand cmd = new SqlCommand(sql, sqlCon);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                Console.WriteLine("Execute:" + ex.Message);
            }
            return -1;
        }


        public int ExecuteReturn(string sql, string TenDangNhap, string MatKhau)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = sqlCon;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter tenDangNhap = new SqlParameter("@TenDangNhap", TenDangNhap);
                tenDangNhap.DbType = DbType.String;
                SqlParameter matKhau = new SqlParameter("@MatKhau", MatKhau);
                matKhau.DbType = DbType.String;
                SqlParameter returnValue = new SqlParameter("@result", SqlDbType.Int);
                returnValue.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(returnValue);

                cmd.Parameters.Add(tenDangNhap);
                cmd.Parameters.Add(matKhau);
                cmd.ExecuteNonQuery();
                int result = int.Parse(returnValue.Value.ToString());
                return result;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                Console.WriteLine("Execute:" + ex.Message);
                return -1;
            }
        }

    }
}